package zz.extjs.util;

import org.json.JSONArray;
import org.json.JSONObject;

import zz.extjs.factory.DAOFactory;



public class Test {

	public static void main(String args[]) {

		/*Member mem = new Member();
		mem.setUsername("jxp");
		mem.setPassword("zzz");

		if (!mem.isEmpty()) {

			// 用户名、密码不为空，验证
			try {

				// 判断用户名、密码的合法性
				if (DAOFactory.getMemberInstance().isUser(mem)) {

					System.out.println("MD5 Test suite:");

				} else {

					System.out.println("用户名或密码错误！");
				}
			} catch (Exception e) {

				System.out.println(e);
			}

		}*/
		/*String sql = "";
		Iterator<Student> stuit ;
		try {
			
			JSONArray js = DAOFactory.getDatasetDAOInstance().getSchList();       
			
			System.out.println(js);
			
			
			//sql = "select a.*,c.COS_NAME from (";
			for(int i=0; i<js.length(); i++){
				JSONObject jo = (JSONObject)(js.get(i));
				if(jo!=null && !jo.isNull("text") && !"".equals(jo.getString("text"))){
					sql += " union ";
					sql += "select p.*,'"+jo.getString("text")+"' as term from DUSC"+jo.getString("text")+".PERSON_INFO_TAB p";
				}
			} 
			System.out.println(" union ".length());
			sql = sql.substring(" union ".length());
			sql = "select a.*,c.COS_NAME from (" + sql;
			sql += ") a left join COURSE_TAB c on a.COS_ID=c.COS_ID";
			sql += " order by a.T_SID ";

			stuit = DAOFactory.getStudentInstance().getList(sql);

			
			System.out.println(stuit.next().getEname());

		} catch (Exception e) {

			System.out.println(e);
		}
		*/
		/*String flag = "insert";
		//创建临时表
		String sqlCreateTmp = "declare global temporary table  Session.CRS_STU_TEMP(PER_ID VARCHAR(32) not null,T_SID VARCHAR(32) not null,"
			+"E_NAME VARCHAR(200),C_NAME VARCHAR(64),K_NAME VARCHAR(64),PB_ID VARCHAR(64),T_BIRTHDAY DATE,T_SEX VARCHAR(32),"
			+"T_HATEL VARCHAR(32),HAS_PHOTO INTEGER,MEMO VARCHAR(1020),HAS_STUCARD INTEGER,PB_TYPE VARCHAR(32),PB_TIMEOUT DATE,"
			+"PASS_ID VARCHAR(32),T_PHONE VARCHAR(64),T_CLASS VARCHAR(32),CUR_CLASS VARCHAR(32),T_GRADE VARCHAR(32),T_STUDY VARCHAR(256),"
			+"T_ELECTIVE VARCHAR(256),COS_ID VARCHAR(32) default '0',P_W VARCHAR(224),T_PBOUT VARCHAR(25),T_PBBEGIN DATE"
			+") ON COMMIT PRESERVE ROWS  not logged;";
		
		int r = -1;
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		try {
			dbc = new DataBaseConnection();
			String drivername = dbc.getDBDRIVER();
			try{
				//判断临时表是否存在,不存在创建临时表
				ResultSet rs = dbc.getConnection().prepareStatement("select * from Session.CRS_STU_TEMP").executeQuery();
			} catch (SQLException e) {
				r = e.getErrorCode();
			} 


			if(r<0){
				//dbc.getConnection().prepareStatement(sqlCreateTmp).executeUpdate();

				Connection a = dbc.getConnection();
				Statement stmt=a.createStatement();
	             r = stmt.executeUpdate(sqlCreateTmp);
			}
			r = dbc.getConnection().prepareStatement("select * from CRS_STU_TEMP").executeUpdate();
			r = pstmt.executeUpdate();

			if ("select".equals(flag)) {
				ResultSet rs = pstmt.executeQuery();
				if (rs.next()) // 有记录返回
					r = 1;
				rs.close();
			} else
				r = pstmt.executeUpdate();
			pstmt.close();
			// r = 1;
		} catch (SQLException e) {
			r = e.getErrorCode();
		} finally {
			dbc.close();
		}
*/
		/*int r = -1;
		String sqlcreate = "", sqlinsert="",sqlselect="";
		sqlcreate = "declare global temporary table  Session.CRS_STU_TEMP(PER_ID VARCHAR(32),T_SID VARCHAR(32)"
			+") ON COMMIT PRESERVE ROWS  not logged";
		sqlinsert="insert into Session.CRS_STU_TEMP(PER_ID,T_SID) " +
		"select PER_ID,T_SID from PERSON_INFO_TAB";
		sqlselect = "select * from Session.CRS_STU_TEMP";
		//PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		try {
			//java.lang.Class.forName("org.apache.derby.jdbc.ClientDriver");
			java.lang.Class.forName("oracle.jdbc.OracleDriver");
			Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@210.28.70.239:1521:orcl", "dusc1001", "dusc");//"jdbc:derby://localhost:1527/orcl", "dusc0601", "dusc");

			
			//r = stmt.executeUpdate(sqlcreate);
			//r = stmt.executeUpdate(sqlinsert);
			//ResultSet rs=stmt.executeQuery(sqlselect);
			
			dbc = new DataBaseConnection();
			dbc.setConnection(conn);
			
			String strdrv = dbc.getConnection().toString();
			Statement stmt=dbc.getConnection().createStatement();
			if(strdrv!=null && strdrv.indexOf("oracle")>=0){	//检查是连接oracle还是Derby,对oracle需先检查临时表是否存在，存在则只清空，而不创建
				try{
				r = stmt.executeUpdate("select * from CRS_STU_TEMP");	
				}catch(Exception e){
					r=-1;
					System.err.println(e.getMessage());
				}
				if(r < 0){
					sqlcreate = "CREATE global temporary table CRS_STU_TEMP (" +
							"PER_ID VARCHAR2(32) NOT NULL,	T_SID VARCHAR2(8) NOT NULL)ON COMMIT PRESERVE ROWS";
				}else
					sqlcreate = "CRS_STU_TEMP";
				sqlinsert="insert into CRS_STU_TEMP(PER_ID,T_SID) " +
				"select PER_ID,T_SID from PERSON_INFO_TAB";
				sqlselect = "select * from CRS_STU_TEMP";
			}
			
			if("CRS_STU_TEMP".equals(sqlcreate))	//在orcale时，省略创建临时表步骤
				System.out.println(stmt.executeUpdate("delete from CRS_STU_TEMP"));
			else
				System.out.println(stmt.executeUpdate(sqlcreate));
			System.out.println(stmt.executeUpdate(sqlinsert));
			ResultSet rs=stmt.executeQuery(sqlselect);
			//System.out.println(dbc.getConnection().prepareStatement(sqlcreate).executeUpdate());
			//System.out.println(dbc.getConnection().prepareStatement(sqlinsert).executeUpdate());
			//ResultSet rs=dbc.getConnection().prepareStatement(sqlselect).executeQuery();
			r=0;
			while (rs.next()){ // 有记录返回
				r ++;
		}
		System.out.println(r);
        }
          catch(Exception e1){
            System.err.println("execsql.executeUpdate:"+e1.getMessage());
        } finally {
  			dbc.close();
  		}
          */
		
		/*try {
		//Iterator it =DAOFactory.getCrossSchemaInstance().getAllTermList(null);
			JSONArray js = DAOFactory.getCrossSchemaInstance().getSchList() ;
			String str = "";
			for(int j=0; j<js.length(); j++)
				str += ",#\""+((JSONObject)js.get(j)).getString("text")+"\"@";		//#"0702"@,#"1001"@,#"1002"@

			JSONObject sqljo = new JSONObject();
			sqljo.put("sqlcreate_derby",
				"declare global temporary table  Session.CRS_STU_TEMP("
				+ "PER_ID VARCHAR(32) NOT NULL,T_SID VARCHAR(8) NOT NULL,E_NAME VARCHAR(50),"
				+ "C_NAME VARCHAR(16),K_NAME VARCHAR(16),PB_ID VARCHAR(16),T_BIRTHDAY DATE,"
				+ "T_SEX VARCHAR(8),T_HATEL VARCHAR(8),HAS_PHOTO INTEGER,MEMO VARCHAR(255),"
				+ "HAS_STUCARD INTEGER,PB_TYPE VARCHAR(16),PB_TIMEOUT DATE,PASS_ID VARCHAR(8),"
				+ "T_PHONE VARCHAR(16),T_CLASS VARCHAR(8),CUR_CLASS VARCHAR(8),T_GRADE VARCHAR(8),"
				+ "T_STUDY VARCHAR(64),T_ELECTIVE VARCHAR(64),COS_ID VARCHAR(32) DEFAULT '0',"
				+ "P_W VARCHAR(56),T_PBOUT VARCHAR(25),T_PBBEGIN DATE"
				+ str.replaceAll("@"," VARCHAR(32)").replaceAll("#", "") 			//, "0702" VARCHAR(32) , "1001" VARCHAR(32),....
				+ ") ON COMMIT PRESERVE ROWS  not logged");
			sqljo.put("sqlinsert_derby",
					"insert into Session.CRS_STU_TEMP(" +
					"PER_ID, T_SID, E_NAME,C_NAME, K_NAME, PB_ID,T_BIRTHDAY, T_SEX, T_HATEL,HAS_PHOTO, MEMO, " +
					"HAS_STUCARD,PB_TYPE, PB_TIMEOUT, PASS_ID,T_PHONE, T_CLASS, CUR_CLASS,T_GRADE, T_STUDY, " +
					"T_ELECTIVE,COS_ID, P_W, T_PBOUT,T_PBBEGIN" +
					str.replaceAll("#", "").replaceAll("@", "") +		//,0702,0601,....
					")SELECT P.*" +
					str.replaceAll("#", "'NULL' ").replaceAll("@", "") +	//,'null' "0702",'null' "0601",....
					" FROM ");					//执行查询时动态装配
			//System.out.println(sqljo.getString("sqlcreate_derby"));
			//System.out.println(sqljo.getString("sqlinsert_derby"));
			sqljo.put("sqlselect_derby", "select * from Session.CRS_STU_TEMP");

			sqljo.put("sqlcreate_oracle",
				"CREATE GLOBAL TEMPORARY TABLE CRS_STU_TEMP ("
				+ "PER_ID VARCHAR2(32 CHAR) NOT NULL,T_SID VARCHAR2(8 CHAR) NOT NULL,E_NAME VARCHAR2(50 CHAR),"
				+ "C_NAME VARCHAR2(16 CHAR),K_NAME VARCHAR2(16 CHAR),PB_ID VARCHAR2(16 CHAR),T_BIRTHDAY DATE,"
				+ "T_SEX VARCHAR2(8 CHAR),T_HATEL VARCHAR2(8 CHAR),HAS_PHOTO NUMBER(1),MEMO VARCHAR2(255 CHAR),"
				+ "HAS_STUCARD NUMBER(1),PB_TYPE VARCHAR2(16 CHAR),PB_TIMEOUT DATE,PASS_ID VARCHAR2(8 CHAR),"
				+ "T_PHONE VARCHAR2(16 CHAR),T_CLASS VARCHAR2(8 CHAR),CUR_CLASS VARCHAR2(8 CHAR),T_GRADE VARCHAR2(8 CHAR),"
				+ "T_STUDY VARCHAR2(64 CHAR),T_ELECTIVE VARCHAR2(64 CHAR),COS_ID VARCHAR2(32 CHAR) DEFAULT 0,"
				+ "P_W VARCHAR2(56 CHAR),T_PBOUT VARCHAR2(25 BYTE),T_PBBEGIN DATE"
				+ str.replaceAll("@"," VARCHAR2(32 CHAR)").replaceAll("#", "") 			//, "0702" VARCHAR2(32 CHAR) , "1001" VARCHAR2(32 CHAR),....
				+ ")ON COMMIT PRESERVE ROWS");
			sqljo.put("sqlinsert_oracle", "insert into CRS_STU_TEMP(" +
					"PER_ID, T_SID, E_NAME,C_NAME, K_NAME, PB_ID,T_BIRTHDAY, T_SEX, T_HATEL,HAS_PHOTO, MEMO, " +
					"HAS_STUCARD,PB_TYPE, PB_TIMEOUT, PASS_ID,T_PHONE, T_CLASS, CUR_CLASS,T_GRADE, T_STUDY, " +
					"T_ELECTIVE,COS_ID, P_W, T_PBOUT,T_PBBEGIN " +
					str.replaceAll("#", "").replaceAll("@", "") +		//,0702,0601,....
					")SELECT P.*" +
					str.replaceAll("#", "'NULL' ").replaceAll("@", "") +	//,'null' "0702",'null' "0601",....
					" FROM ");						//执行查询时动态装配
			sqljo.put("sqlselect_oracle", "select * from CRS_STU_TEMP");
			
			String sql = "";
			for(int i=0; i<js.length(); i++){
				sql = sqljo.getString("sqlinsert_derby");
				String st = ((JSONObject)js.get(i)).getString("text");
				sql = sql.replaceFirst(",'NULL' \""+st+"\"", ",c.COS_NAME \""+st+"\"");
				sql += " DUSC"+st+".PERSON_INFO_TAB P left join DUSC"+st+".COURSE_TAB c on p.COS_ID=c.COS_ID "; 
				System.out.println(sql);
			}
		int r=0;
		//	while(it.next()!=null)
		//		r++;
		//	System.out.println(r);
		} catch (Exception e) {
            System.err.println("execsql.executeUpdate:"+e.getMessage());
		}
*/
try {
	DAOFactory.getCrossSchemaInstance().getAllTermList(null);
} catch (Exception e) {
	// TODO: handle exception
}

	}

}
